﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Arizu.DAL;
using Arizu.Entity;
using System.Net.Mail;
using System.Net;

namespace Arizu.Repository
{
    public class Main : BaseProvider
    {
        #region 创建实例
        private static volatile Main instance;
        private static object lockHelper = new object();

        private Main()
        {
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public static Main CreateInstance()
        {
            if (instance == null)
            {
                lock (lockHelper)
                {
                    if (instance == null)
                    {
                        instance = new Main();
                    }
                }
            }
            return instance;
        }
        #endregion

        /// <summary>
        /// 发布的前14个房间按照时间降序排列
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public DataTable GetList()
        {
            DataTable dt = new DataTable();
            string sql = "select top 14 a.areaID,a.areaName as name,r.RoomTitle as title,r.NightMoney as roommoney from area ";
            sql += " as a join RoomInfo as r on a.areaID=r.AreaID where r.IsSend=0 order by r.SendTime desc";
            try
            {
                dt = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        /// <summary>
        /// 选择省
        /// </summary>
        /// <returns></returns>
        public DataTable GetShengList()
        {
            DataTable dt = new DataTable();
            string sql = "select provinceID,provinceName from province";
            try
            {
                dt = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql).Tables[0];
            }
            catch (Exception)
            {

                throw;
            }
            return dt;
        }

        /// <summary>
        /// 根据省id获取市列表
        /// </summary>
        /// <param name="shengid"></param>
        /// <returns></returns>
        public DataTable GetCityList(string shengid)
        {
            DataTable dt = new DataTable();
            string sql = "select cityID,cityName from city where cityFather='" + shengid + "'";
            try
            {
                dt = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql).Tables[0];
            }
            catch (Exception)
            {

                throw;
            }
            return dt;
        }

        public DataTable GetCityID(string cityName)
        {
            DataTable dt = new DataTable();
            string sql = "select cityID from city where cityName='" + cityName + "'";
            try
            {
                dt = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql).Tables[0];
            }
            catch (Exception)
            {

                throw;
            }
            return dt;
        }

        /// <summary>
        /// 根据市id获取区域列表
        /// </summary>
        /// <param name="cityid"></param>
        /// <returns></returns>
        public DataTable GetAreaList(string cityid)
        {
            DataTable dt = new DataTable();
            string sql = "select areaID,areaName from area where areaFather='" + cityid + "'";
            try
            {
                dt = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql).Tables[0];
            }
            catch (Exception)
            {

                throw;
            }
            return dt;
        }

        /// <summary>
        /// 插入房间信息
        /// </summary>
        /// <param name="sr"></param>
        /// <returns></returns>
        public int InsertInfo(SendRoom sr)
        {
            int i = 0;
            string mysql = "";
            string sql = "INSERT INTO SendRoom(Name,Phone,Email,Province,City,Area,Adressdesc,RoomType,OftenServer,DuoTaoFangYuan,RoomNum,FaPiao,JiaGe,Issend,ownerID,IsTeSe,IsTeJia)VALUES(@xingming,@phone,@email,@sheng,@city,@quyu,@adressdesc,@roomtype,@oftenserver,@duotaofangyuan,@roomnum,@fapiao,@jiage,@issend,@ownerid,@IsTeSe,@IsTeJia);;select @@identity;";
            #region parameters
            SqlParameter parm_Name = new SqlParameter("@xingming", sr.Name);
            SqlParameter parm_Phone = new SqlParameter("@phone", sr.Phone);
            SqlParameter parm_Email = new SqlParameter("@email", sr.Email);
            SqlParameter parm_Province = new SqlParameter("@sheng", sr.Province);
            SqlParameter parm_City = new SqlParameter("@city", sr.City);
            SqlParameter parm_Area = new SqlParameter("@quyu", sr.Area);
            SqlParameter parm_Adressdesc = new SqlParameter("@adressdesc", sr.Adressdesc);
            SqlParameter parm_RoomType = new SqlParameter("@roomtype", sr.RoomType);
            SqlParameter parm_OftenServer = new SqlParameter("@oftenserver", sr.OftenServer);
            SqlParameter parm_DuoTaoFangYuan = new SqlParameter("@duotaofangyuan", sr.DuoTaoFangYuan);
            SqlParameter parm_RoomNum = new SqlParameter("@roomnum", sr.RoomNum);
            SqlParameter parm_FaPiao = new SqlParameter("@fapiao", sr.FaPiao);
            SqlParameter parm_JiaGe = new SqlParameter("@jiage", sr.JiaGe);
            SqlParameter parm_Issend = new SqlParameter("@issend", sr.Issend);
            SqlParameter parm_ownerID = new SqlParameter("@ownerid", sr.OwnerID);
            SqlParameter parm_IsTeJia = new SqlParameter("@issend", sr.IsTeJia);
            SqlParameter parm_IsTeSe = new SqlParameter("@issend", sr.IsTeSe);

            SqlParameter[] parameters = { parm_Name, parm_Phone, parm_Email, parm_Province, parm_City, parm_Area, parm_Adressdesc, parm_RoomType, parm_OftenServer, parm_DuoTaoFangYuan, parm_RoomNum, parm_FaPiao, parm_JiaGe, parm_Issend, parm_ownerID, parm_IsTeJia, parm_IsTeSe };
            #endregion
            try
            {
                sr.SendRoomid = int.Parse(SqlHelper.ExecuteDataset(conn, CommandType.Text, sql, parameters).Tables[0].Rows[0][0].ToString());
                mysql = "INSERT INTO Price(DayPrice,RoomID)values('" + sr.JiaGe + "'," + sr.SendRoomid + ")";
                i = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, mysql);
                //int.Parse(SqlHelper.ExecuteDataset(conn, CommandType.Text, mysql, parameters).Tables[0].Rows[0][0].ToString());
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
            return i;
        }

        /// <summary>
        /// 获取发布前14个房间
        /// </summary>
        /// <returns></returns>
        public DataTable GetSendRoom(string city)
        {
            DataTable dt = new DataTable();
            string sql = "select top 14 * from SendRoom where IsTeJia=1 and Issend=1 and City='" + city + "' order by SendTime desc";
            try
            {
                dt = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        /// <summary>
        /// 根据sendroomid，获取数据
        /// </summary>
        /// <param name="einzugid"></param>
        /// <returns></returns>
        public SendRoom GetMember(int sendroomid)
        {
            SendRoom list = new SendRoom();
            string sql = "select * from SendRoom where sendroomid=" + sendroomid;
            try
            {
                DataTable tbl = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql).Tables[0];

                using (DataTableReader reader = tbl.CreateDataReader())
                {
                    while (reader.Read())
                    {
                        list = DynamicEntityBuilder<SendRoom>.CreateBuilder(reader).Build(reader);
                    }
                }
                return list;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 根据sendroomid，获取图片数据
        /// </summary>
        /// <param name="einzugid"></param>
        /// <returns></returns>
        public ImgSendRoom GetImg(int sendroomid)
        {
            ImgSendRoom list = new ImgSendRoom();
            string sql = "select * from ImageSendRoom where SendRoomid=" + sendroomid;
            try
            {
                DataTable tbl = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql).Tables[0];

                using (DataTableReader reader = tbl.CreateDataReader())
                {
                    while (reader.Read())
                    {
                        list = DynamicEntityBuilder<ImgSendRoom>.CreateBuilder(reader).Build(reader);
                    }
                }
                return list;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 根据sendroomid，获取图片数据
        /// </summary>
        /// <param name="einzugid"></param>
        /// <returns></returns>
        public int GetImgcount(int sendroomid)
        {
            int count = 0;
            string sql = "select count(*) from ImageSendRoom where SendRoomid=" + sendroomid;
            try
            {
                DataTable tbl = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql).Tables[0];
                if (tbl.Rows.Count > 0)
                {
                    count = int.Parse(tbl.Rows[0][0].ToString());
                }
                return count;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 根据sendroomid，获取图片数据
        /// </summary>
        /// <param name="einzugid"></param>
        /// <returns></returns>
        public DataTable GetImgList(int sendroomid)
        {
            string sql = "select * from ImageSendRoom where SendRoomid=" + sendroomid;
            try
            {
                DataTable tbl = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql).Tables[0];
                return tbl;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 获取前12个特色房间
        /// </summary>
        /// <returns></returns>
        public DataTable GetSendRoomByTeSe()
        {
            DataTable dt = new DataTable();
            string sql = "select top 12 * from SendRoom where IsTeSe=1 and  Issend=1 order by SendTime desc";
            try
            {
                dt = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        /// <summary>
        /// 获取前6个特色房间
        /// </summary>
        /// <returns></returns>
        public DataTable GetEvaluationRoom()
        {
            DataTable dt = new DataTable();
            string sql = "select top 6 e.SendRoomid,p.Image,s.RoomTitle,e.EvaluationContent,p.Xing,p.Ming from EvaluationRoom as e join PersonInfo as p on e.PersonId=p.PersonId join SendRoom as s on e.SendRoomid=s.SendRoomid order by id desc";
            try
            {
                dt = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        /// <summary>
        /// 根据SendRoomid，获取房东信息
        /// </summary>
        /// <returns></returns>
        public DataTable Getpeopleinfo(string sendroomid)
        {
            DataTable dt = new DataTable();
            string sql = "select * from PersonInfo where PersonId in (select peopleid from RoomByPeople where sendroomid=" + sendroomid + ")";
            try
            {
                dt = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        /// <summary>
        /// 根据SendRoomid，获取房东发布的其他房间信息
        /// </summary>
        /// <returns></returns>
        public DataTable Getfangdongsendroominfo(string sendroomid)
        {
            DataTable dt = new DataTable();
            string sql = "select s.SendRoomid,s.RoomTitle,s.JiaGe,i.Image0 from SendRoom as s join ImageSendRoom as i on s.SendRoomid=i.SendRoomid where s.SendRoomid in (";
            sql += "select sendroomid from RoomByPeople where peopleid in (select peopleid from RoomByPeople where sendroomid=" + sendroomid + ")) and s.SendRoomid not in (" + sendroomid + ")";
            try
            {
                dt = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        /// <summary>
        /// 根据SendRoomid，获取房间的评论
        /// </summary>
        /// <returns></returns>
        public DataTable Getroompinglun(string sendroomid)
        {
            DataTable dt = new DataTable();
            string sql = "select e.EvaluationContent,e.EvaluationDatetime,e.SendRoomid,p.Image,p.Xing,p.Ming from EvaluationRoom as e join PersonInfo as p on e.PersonId=p.PersonId where SendRoomid=" + sendroomid;
            try
            {
                dt = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        /// <summary>
        /// 发送邮件
        /// </summary>
        /// <param name="frommail"></param>
        /// <param name="tomail"></param>
        /// <param name="maittitle"></param>
        /// <param name="body"></param>
        public static void SendEmail(string frommail, string tomail, string maittitle, string body)
        {
            MailMessage message = new MailMessage();
            message.From = new MailAddress(frommail);
            message.To.Add(new MailAddress(tomail));
            message.Subject = maittitle;
            message.Body = body;
            message.IsBodyHtml = true;
            SmtpClient smtpClient = new SmtpClient("smtp.163.com");
            smtpClient.Credentials = new NetworkCredential("服务器端邮件账号", "邮箱密码");
            smtpClient.Send(message);
        }

        /// <summary>
        /// 后台获取全部房间信息
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable GetAllRoom(string sql)
        {
            DataTable dt = new DataTable();
            try
            {
                dt = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        /// <summary>
        /// 删除房间
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int DelRoom(string sql)
        {
            int i = 0;
            try
            {
                i = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql);
            }
            catch (Exception)
            {
                throw;
            }
            return i;
        }

        /// <summary>
        /// 根据roomid获取房东的姓名
        /// </summary>
        /// <param name="roomid"></param>
        /// <returns></returns>
        public DataTable GetName(string roomid)
        {
            DataTable dt = new DataTable();
            string sql = "select p.Xing,p.Ming from SendRoom as s join PersonInfo as p on s.OwnerID=p.PersonId where s.SendRoomid=" + roomid;
            try
            {
                dt = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        /// <summary>
        /// 根据roomid获取房间信息
        /// </summary>
        /// <param name="roomid"></param>
        /// <returns></returns>
        public DataTable GetRoom(string roomid)
        {
            DataTable dt = new DataTable();
            string sql = "select * from SendRoom where SendRoomid=" + roomid;
            try
            {
                dt = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        /// <summary>
        /// 保存房间信息
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int SaveRoom(string sql)
        {
            int i = 0;
            try
            {
                i = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql);
            }
            catch (Exception)
            {
                throw;
            }
            return i;
        }

        /// <summary>
        /// 获取房间图片数量
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable ImgRoomNum(string roomid)
        {
            DataTable dt = new DataTable();
            string sql = "select * from ImageSendRoom where SendRoomid=" + roomid;
            try
            {
                dt = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        /// <summary>
        /// 插入房间图片数据库
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int InsertRoom(string sql)
        {
            int i = 0;
            try
            {
                i = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql);
            }
            catch (Exception)
            {
                throw;
            }
            return i;
        }

        /// <summary>
        /// 删除会员
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int DelPerson(string sql)
        {
            int i = 0;
            try
            {
                i = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql);
            }
            catch (Exception)
            {
                throw;
            }
            return i;
        }

        /// <summary>
        /// 根据personid获取房东信息
        /// </summary>
        /// <param name="roomid"></param>
        /// <returns></returns>
        public DataTable GetPersonInfo(string personid)
        {
            DataTable dt = new DataTable();
            string sql = "select * from PersonInfo where PersonId=" + personid;
            try
            {
                dt = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        /// <summary>
        /// 保存房东信息
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int SavePersonInfo(string sql)
        {
            int i = 0;
            try
            {
                i = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql);
            }
            catch (Exception)
            {
                throw;
            }
            return i;
        }

        /// <summary>
        /// 插入会员信息
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int InsertPersonInfo(string sql)
        {
            int i = 0;
            try
            {
                i = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql);
            }
            catch (Exception)
            {
                throw;
            }
            return i;
        }
    }
}
